package de.ihk.datamanagment;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.Serializable;
import java.sql.*;

import javax.faces.bean.ManagedBean;

import de.ihk.data.Test;

@ManagedBean
public class MysqlConnector implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private Connection con = null;
	private Statement stmt = null;
	private ResultSet rs = null;
	PreparedStatement addStudent = null;
	PreparedStatement addAdmin = null;
	PreparedStatement addBearbeiter = null;
	PreparedStatement addTest = null;
	PreparedStatement addAufgabenObject = null;

	public MysqlConnector() {
		try {
			Class.forName("com.mysql.jdbc.Driver").getInterfaces();
			con = DriverManager.getConnection("jdbc:mysql://localhost/ihk", "root", "doo0loo");
			stmt = con.createStatement();
			addStudent = con
					.prepareStatement("INSERT INTO students(pruefnr, name, vorname, firma, ort, beruf, bbs) VALUES(?, ?, ?, ?, ?, ?, ?)");
			addAdmin = con.prepareStatement("INSERT INTO admins(id, name, vorname, eMail, password, superadmin) VALUES(?, ?, ?, ?, ?, ?)");
			addBearbeiter = con.prepareStatement("INSERT INTO bearbeiter(id, name, eMail, kuerzel, tests) VALUES(?, ?, ?, ?, ?)");
			addTest = con.prepareStatement("INSERT INTO tests(id, semester, fachgebiet, name, datum, bearbeiter) VALUES(?, ?, ?, ?, ?, ?)");
			addAufgabenObject = con.prepareStatement("INSERT INTO aufgaben(id, name, object) VALUES(?, ?, ?)");

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void mysqlUpdateQuery(String query) throws SQLException {
		stmt.executeUpdate(query);
	}

	public void addStudentToDb(int pruefnr, String name, String vorname, String firma, String ort, String beruf, String bbs)
			throws SQLException {
		addStudent.setInt(1, pruefnr);
		addStudent.setString(2, name);
		addStudent.setString(3, vorname);
		addStudent.setString(4, firma);
		addStudent.setString(5, ort);
		addStudent.setString(6, beruf);
		addStudent.setString(7, bbs);
		addStudent.executeUpdate();
		System.out.println("INSERT INTO students(pruefnr, name, vorname, firma, ort, beruf, bbs) VALUES('" + pruefnr + "', '" + name
				+ "e', '" + vorname + "', '" + firma + "', '" + ort + "', '" + beruf + "', '" + bbs + "')");
		System.out.println("Done");

	}

	public void addAdminToDb(int id, String name, String vorname, String eMail, String password, int superadmin) throws SQLException {
		addAdmin.setInt(1, id);
		addAdmin.setString(2, name);
		addAdmin.setString(3, vorname);
		addAdmin.setString(4, eMail);
		addAdmin.setString(5, password);
		addAdmin.setInt(6, superadmin);
		addAdmin.executeUpdate();
		System.out.println("INSERT INTO admins(id, name, vorname, eMail, password, superadmin) VALUES(" + id + "," + name + ", " + vorname
				+ ", " + eMail + ", " + password + ", " + superadmin + ")");
		System.out.println("Done");
	}

	public void addBearbeiterToDb(int id, String name, String eMail, String kuerzel, Test[] tests) throws SQLException {
		addBearbeiter.setInt(1, id);
		addBearbeiter.setString(2, name);
		addBearbeiter.setString(3, eMail);
		addBearbeiter.setString(4, kuerzel);
		String testsString = "";
		// TODO tests ids in testString
		addBearbeiter.setString(5, testsString);
		addBearbeiter.executeUpdate();
		System.out.println("INSERT INTO bearbeiter(id, name, eMail, kuerzel, tests) VALUES(" + id + "," + name + ", " + eMail + ", "
				+ kuerzel + ", " + testsString + ")");
		System.out.println("Done");
	}

	public void addTestToDb(int id, String semester, String fachgebiet, String name, String datum, String bearbeiter) throws SQLException {
		addTest.setInt(1, id);
		addTest.setString(2, semester);
		addTest.setString(3, fachgebiet);
		addTest.setString(4, name);
		addTest.setString(5, datum);
		addTest.setString(6, bearbeiter);
		System.out.println("INSERT INTO tests(id, semester, fachgebiet, name, datum, bearbeiter) VALUES(" + id + "," + semester + ", "
				+ fachgebiet + ", " + name + ", " + datum + ", " + bearbeiter + ")");
		addTest.executeUpdate();
		System.out.println("Done");
	}

	public void addAufgabenObjectToDb(int id, String name, Object object) throws SQLException {
		System.out.println("INSERT INTO aufgaben(id, name, object) VALUES(" + id + ", " + name + ", " + object + ")");
		addAufgabenObject.setInt(1, id);
		addAufgabenObject.setString(2, name);
		addAufgabenObject.setObject(3, object);
		addAufgabenObject.executeUpdate();
	}

	private static final String SQL_SERIALIZE_OBJECT = "INSERT INTO aufgaben(name, object) VALUES (?, ?)";
	private static final String SQL_DESERIALIZE_OBJECT = "SELECT object FROM aufgaben WHERE id = ?";

	public Object deSerializeJavaObjectFromDB(int serialized_id) throws SQLException, IOException, ClassNotFoundException {
		PreparedStatement pstmt = con.prepareStatement(SQL_DESERIALIZE_OBJECT);
		pstmt.setInt(1, serialized_id);
		ResultSet rs = pstmt.executeQuery();
		rs.next();

		// Object object = rs.getObject(1);

		byte[] buf = rs.getBytes(1);
		ObjectInputStream objectIn = null;
		if (buf != null)
			objectIn = new ObjectInputStream(new ByteArrayInputStream(buf));

		Object deSerializedObject = objectIn.readObject();

		rs.close();
		pstmt.close();

		System.out.println("Java object de-serialized from database. Object: " + deSerializedObject + " Classname: "
				+ deSerializedObject.getClass().getName());
		return deSerializedObject;
	}

	public int serializeJavaObjectToDB(Object objectToSerialize) throws SQLException {

		PreparedStatement pstmt = con.prepareStatement(SQL_SERIALIZE_OBJECT);

		// just setting the class name
		pstmt.setString(1, objectToSerialize.getClass().getName());
		pstmt.setObject(2, objectToSerialize);
		pstmt.executeUpdate();
		ResultSet rs = pstmt.getGeneratedKeys();
		int serialized_id = -1;
		if (rs.next()) {
			serialized_id = rs.getInt(1);
		}
		rs.close();
		pstmt.close();
		System.out.println("Java object serialized to database. Object: " + objectToSerialize);
		return serialized_id;
	}

	public ResultSet getStudents() throws SQLException {
		rs = stmt.executeQuery("SELECT pruefnr,name, vorname, firma, ort, beruf, bbs FROM students");
		return rs;
	}

	public ResultSet getAdmins() throws SQLException {
		rs = stmt.executeQuery("SELECT id, name, vorname, eMail, password, superadmin FROM admins");
		return rs;
	}

	public ResultSet getBearbeiters() throws SQLException {
		rs = stmt.executeQuery("SELECT id, name, eMail, kuerzel, tests FROM bearbeiter");
		return rs;
	}

	public ResultSet getTests() throws SQLException {
		rs = stmt.executeQuery("SELECT id, semester, fachgebiet, name, datum, bearbeiter, aufgabenid FROM tests");
		return rs;
	}

	public ResultSet getAufgaben() throws SQLException {
		rs = stmt.executeQuery("SELECT id, name, object FROM aufgaben");
		return rs;
	}

	public ResultSet checkLogin() throws SQLException {
		rs = stmt.executeQuery("SELECT id, name, vorname, eMail, password, superadmin FROM admins");
		return rs;
	}

}
